package sui.utils.excel.test;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;

import sui.utils.encrypt.MD5Utils;
import sui.utils.excel.ReaderExcelUtils;

/**
 * <p>
 * Description: 读取Excel生成SQL(MySQL)
 * </p>
 * @author XQF SUI
 * @created 2017年4月21日 上午10:22:00
 * @version 1.0
 */
public class ReaderExcelAnalysisData {

	/**
	 * Excel文件路径
	 */
	private static final String EXCEL_FILE_PATH = "F:\\window\\Desktop\\2017-08-21_重复数据-宜管家.xlsx";
	
	/**
	 * Excel sheet页坐标
	 */
	private static final Integer EXCEL_SHEET_INDEX = 0;
	// 沟通记录状态坐标
	private static final Integer STATUS_COMM_INDEX = 10;
	// 市场活动状态坐标
	private static final Integer STATUS_ACTIVITY_INDEX = 12;
	// 开户状态坐标
	private static final Integer STATUS_OPEN_ACCOUNT_INDEX = 8;
	// 邀约陪访状态坐标
	private static final Integer STATUS_INVITATION_ACCOMPANY_INDEX = 11;
	
	private static List<String> exceptionList = new ArrayList<>();
	private static List<String> delList = new ArrayList<>();
//	private static Map<String, String> map = new HashMap<>();
	
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
		Workbook workbook = WorkbookFactory.create(new File(EXCEL_FILE_PATH));
//		readerExcel(workbook);
		Map<String, Set<CustomerEntity>> map = grouping(workbook);
		analysis(map);
	}
	
//	private static void readerExcel(Workbook workbook) {
//		
//		Sheet sheet = workbook.getSheetAt(3);
//		
//		for (Row row : sheet) {
//			String value = ReaderExcelUtils.getStringCellValue(row.getCell(10));
//			if (StringUtils.isNotBlank(value) && !"null".equals(value.trim().toLowerCase())) {
//				map.put(ReaderExcelUtils.getStringCellValue(row.getCell(0)), ReaderExcelUtils.getStringCellValue(row.getCell(10)));
//			}
//		}
//	}

	private static void analysis(Map<String, Set<CustomerEntity>> map) {
		for (Entry<String, Set<CustomerEntity>> entry : map.entrySet()) {
//			System.out.println("==================================== Group ====================================");
			if (exceptionList.contains(entry.getKey())) {
				Iterator<CustomerEntity> iterator = entry.getValue().iterator();
				while (iterator.hasNext()) {
					CustomerEntity customer = iterator.next();
					delList.add(customer.getId());
				}
			} else {
				CustomerEntity entity = null;
				
				Iterator<CustomerEntity> iterator = entry.getValue().iterator();
				while (iterator.hasNext()) {
					CustomerEntity customer = iterator.next();
					
//					System.out.println(JSON.toJSONString(customer));
					
					if (entity == null) {
						entity = customer;
					} else {
						if (Integer.valueOf(customer.getStatus()) < Integer.valueOf(entity.getStatus())) {
							delList.add(entity.getId());
						} else {
							delList.add(customer.getId());
						}
						
						entity = customer;
					}
				}
			}
		}
		
		System.out.println(JSON.toJSONString(delList));
		
	}

	public static Map<String, Set<CustomerEntity>> grouping(Workbook workbook) throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		// 1.加载Excel
		// 2.按行读取数据
		// 3.判断是否参加市场活动、资产配置、邀约陪访、已开户、沟通记录
		// 4.符合3的数据通过MD5加密创建时间、客户手机号、客户姓名、客户经理作为key放入独立List中
		// 5.将不符合3的数据进行MD5加密创建时间、客户手机号、客户姓名、客户经理作为依据进行分组处理
		// 6.依次遍历分组,4的List是否有当前分组数据,有则删除,无则进行组内比较
		// 7.如一组数据同时存在已开户&未开户，则删除未开户数据
		// 8.如一组数据均未开户，但客户状态同时存在有效&无效的，则删除无效数据
		// 9.如一组数据均未开户且客户状态相同，但沟通记录同时存在有&无的，则删除无的
		
		Map<String, Set<CustomerEntity>> map = new HashMap<>();
		
		Sheet sheet = workbook.getSheetAt(EXCEL_SHEET_INDEX);
		
		for (Row row : sheet) {
			CustomerEntity customer = convertToCustomer(row);
			String key = generateMD5Key(customer);
			
			if (isActive(row)) {
				exceptionList.add(key);
			} else {
				if (map.containsKey(key)) {
					Set<CustomerEntity> set = map.get(key);
					set.add(customer);
					map.put(key, set);
				} else {
					Set<CustomerEntity> set = new HashSet<>();
					set.add(customer);
					map.put(key, set);
				}
			}
		}
		
		return map;
	}
	
	public static String  generateMD5Key(CustomerEntity customer) {
		return MD5Utils.Md5(customer.getName() + customer.getMobile() + customer.getCrm() + customer.getCreateTime(), "123");
	}
	
	private static CustomerEntity convertToCustomer(Row row) {

		CustomerEntity customer = new CustomerEntity();

		customer.setId(ReaderExcelUtils.getStringCellValue(row.getCell(0)));
		customer.setCrm(ReaderExcelUtils.getStringCellValue(row.getCell(4)));
		customer.setName(ReaderExcelUtils.getStringCellValue(row.getCell(2)));
		customer.setMobile(ReaderExcelUtils.getStringCellValue(row.getCell(3)));
		customer.setCreateTime(ReaderExcelUtils.getStringCellValue(row.getCell(6)));
		customer.setStatus(ReaderExcelUtils.getStringCellValue(row.getCell(7)));
		customer.setOpenStatus(ReaderExcelUtils.getStringCellValue(row.getCell(8)));
		customer.setAuditStatus(ReaderExcelUtils.getStringCellValue(row.getCell(9)));
		customer.setCommTime(ReaderExcelUtils.getStringCellValue(row.getCell(10)));
		customer.setInvest(ReaderExcelUtils.getStringCellValue(row.getCell(13)));
		customer.setAccompany(ReaderExcelUtils.getStringCellValue(row.getCell(11)));
		customer.setActivity(ReaderExcelUtils.getStringCellValue(row.getCell(12)));
		
		return customer;
	}


	public static boolean isActive(Row row) {
		
		// 是否开户
		if (ReaderExcelUtils.getStringCellValue(row.getCell(STATUS_OPEN_ACCOUNT_INDEX)).equals("1")) {
			return true;
		}
		
		// 是否参加市场活动
		String activityValue = ReaderExcelUtils.getStringCellValue(row.getCell(STATUS_ACTIVITY_INDEX));
		if (StringUtils.isNotBlank(activityValue) && "1".equals(activityValue)) {
			return true;
		}
		
		// 是否有邀约陪访
		String accompanyValue = ReaderExcelUtils.getStringCellValue(row.getCell(STATUS_INVITATION_ACCOMPANY_INDEX));
		if (StringUtils.isNotBlank(accompanyValue) && "1".equals(accompanyValue)) {
			return true;
		}
		
		// 是否有沟通记录
//		if (map.containsKey(value)) {
//			return true;
//		}
		String commValue = ReaderExcelUtils.getStringCellValue(row.getCell(STATUS_COMM_INDEX));
		if (StringUtils.isNotBlank(commValue) &&  !"null".equals(commValue.trim().toLowerCase())) {
			return true;
		}
		
		return false;
	}
}
